import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import os
import xlsxwriter

plt.rcParams['font.sans-serif'] = ['SimHei']  # 设置中文无衬线字体（黑体）
plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示为方块的问题

# 先制定保存Excel文件的路径
Excel_path = r'D:\Python_Testing\python-py-qt-test2'
Excel_name = r'Test.xlsx'
picture_name = r"Test.png"

# 需要保存的数组
data_1 = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
data_2 = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]) * 2.5

# 需要保存的图像
x = np.linspace(0, 2 * np.pi, 20)
y = np.sin(x) + 2 * np.cos(x)

fig, ax = plt.subplots(figsize=(6, 4))
ax.scatter(x, y, color='blue', marker='o', s=20, edgecolors='black')  # 散点图
ax.plot(x, y, linestyle='-', color='red', linewidth=1.5)  # 直线图


# 先保存文件
plt.savefig(os.path.join(Excel_path, picture_name), dpi=300)
plt.show()

# 再将数组和图片都保存到XLSX文件当中
df1 = pd.DataFrame(data_1, columns=['x', 'y', 'z'], index=['Zernike Z1','Zernike Z2','Zernike Z3'])
df2 = pd.DataFrame(data_2, columns=['x', 'y', 'z'], index=['Zernike Z1','Zernike Z2','Zernike Z3'])

with pd.ExcelWriter(os.path.join(Excel_path, Excel_name), engine='xlsxwriter') as writer:
    df1.to_excel(writer, index=True, startcol=0, startrow=0, sheet_name='Zernike Z1 TEST' )
    df2.to_excel(writer, index=True, startcol=1, startrow=1, sheet_name='Zernike Z2 TEST')

    workbook = writer.book  # 指定工作簿
    worksheet = writer.sheets['Zernike Z1 TEST']  # 选择某个特定的sheet

    # # 设置单元格格式，这是基于库xlsxwriter的
    # cell_format = workbook.add_format({
    #     'bold': True,
    #     'font_color': 'red',
    #     'align': 'center',
    #     'valign': 'vcenter'
    # })
    #
    # worksheet.write('A1', 'Formatted Cell', cell_format)

    # 设置表格的列宽
    worksheet.set_column('A:D', 20)

    # 设置表格的行高
    for row in range(df1.shape[0]):
        worksheet.set_row(row, 20)

    # 读取并导入已经保存在文件夹下的图像
    image_data = os.path.join(Excel_path, picture_name)
    worksheet.insert_image('A8', image_data)



